﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace CarPark.Helper
{
    class SQLHelper
    {
        /// <summary>
        /// 数据库连接信息
        /// </summary>
        public static string Connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Data\Cars.accdb;Persist Security Info=True";
        public static OleDbConnection _Conn = null;
        public static OleDbConnection Conn
        {
            get
            {
                if (_Conn == null || _Conn.State == ConnectionState.Closed)
                {
                    _Conn = new OleDbConnection(Connstr);
                    _Conn.Open();
                }
                return _Conn;
            }
        }
        /// <summary>
        /// 查询返回数据表
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <param name="AParaNames">参数名数组</param>
        /// <param name="AParaValues">参数值数组</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string SQL, string[] AParaNames, object[] AParaValues)
        {
            DataTable Adt = new DataTable();
            try
            {
                OleDbDataAdapter Adp = new OleDbDataAdapter(SQL, Conn);
                if (AParaNames != null)
                {
                    for (int i = 0; i < AParaNames.Length; i++)
                    {
                        
                        OleDbParameter Ap = new OleDbParameter(AParaNames[i], AParaValues[i]);
                        Adp.SelectCommand.Parameters.Add(Ap);
                    }
                }

                Adp.Fill(Adt);
            }
            finally
            {
                //AConn.Close(); AConn.Dispose();
                Conn.Close(); Conn.Dispose();
            }
            return Adt;
        }
        /// <summary>
        /// 返回SQL查询结果的字段值
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="AFieldeName"></param>
        /// <param name="AParaNames"></param>
        /// <param name="AParaValues"></param>
        /// <returns></returns>
        public static object GetDataTableValue(string SQL, string AFieldeName, string[] AParaNames, object[] AParaValues)
        {
            object Ares = null;
            DataTable Adt = GetDataTable(SQL, AParaNames, AParaValues);
            try
            {
                if (Adt != null)
                    if (Adt.Rows.Count > 0)
                        Ares = Adt.Rows[0][AFieldeName];
            }
            finally
            {
                Adt.Dispose();
            }
            return Ares;
        }
        /// <summary>
        /// 返回表根据字段查询的结果
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="AFieldNames"></param>
        /// <param name="AFieldValues"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByField(string SQL, string[] AFieldNames, object[] AFieldValues)
        {
            string TempSQL = "";
            string[] APns = null;
            if (SQL.IndexOf("") >= 0)
                TempSQL = "Select * from (" + SQL + ") a ";
            else
                TempSQL = "Select * from " + SQL;
            if (AFieldNames != null)
            {
                if (AFieldNames.Length > 0)
                {
                    TempSQL = TempSQL + " where (1>0) ";
                    APns = new string[AFieldNames.Length];
                    for (int i = 0; i < AFieldNames.Length; i++)
                    {
                        TempSQL = TempSQL + " and " + AFieldNames[i] + "=@AP" + AFieldNames[i];
                        APns[i] = "@AP" + AFieldNames[i];
                    }
                }
            }
            return GetDataTable(SQL, APns, AFieldValues);
        }

        /// <summary>
        /// 执行 Insert Delete Update
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <param name="AParaNames">参数名数组</param>
        /// <param name="AParaValues">参数值数组</param>
        /// <returns>成功-1;失败-0</returns>
        public static int DoExecute(string SQL, string[] AParaNames, object[] AParaValues)
        {
            int Ares = 0;
            //OleDbConnection AConn = new OleDbConnection(Connstr);
            OleDbCommand Acmd = new OleDbCommand(SQL, Conn);
            try
            {

                if (AParaNames != null)
                {
                    for (int i = 0; i < AParaNames.Length; i++)
                    {
                        OleDbParameter Ap = new OleDbParameter(AParaNames[i], AParaValues[i]);
                        Acmd.Parameters.Add(Ap);
                    }
                }
                Acmd.ExecuteNonQuery();
                Ares = 1;
            }
            catch
            {
                Ares = 0;
            }
            finally
            {
                Acmd.Dispose();
                Conn.Close(); Conn.Dispose();
            }
            return Ares;
        }
    }
}
